﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ZD_Utility;

public partial class CreateDo : System.Web.UI.Page
{
    StringBuilder sb = new StringBuilder();
    DataSet ds = new DataSet();
    SqlConnection con = null;
    string PrimaryKey = string.Empty;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            return;
        }
    }

    public void OpenDataBase(bool Flag)
    {
        sb.AppendFormat("Server={0};", this.Txt_Server.Text);
        if (Flag)
        {
            sb.AppendFormat("Initial Catalog={0};Persist Security Info=True;", this.LB_DataBase.SelectedItem.Text);
        }
        sb.AppendFormat("User={0};", this.Txt_UserName.Text);
        sb.AppendFormat("Pwd={0}", this.Txt_PassWord.Text);
        con = new SqlConnection(sb.ToString());
        try
        {
            con.Open();
        }
        catch (Exception)
        {
            Common.Alert(this.Page, "连接失败，请检查服务器名，用户名，密码是否正确");
        }
    }

    protected void Btn_Validate_Click(object sender, EventArgs e)
    {
        OpenDataBase(false);
        using (SqlCommand com = new SqlCommand())
        {
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = " SELECT * FROM Master..sysdatabases WHERE dbid>6 ORDER BY name ";
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(ds);
            sda.Dispose();
        }

        if (Common.CheckDataSet(ds))
        {
            this.LB_DataBase.DataTextField = "name";
            this.LB_DataBase.DataValueField = "dbid";
            this.LB_DataBase.DataSource = ds.Tables[0];
            this.LB_DataBase.DataBind();
        }
    }

    protected void Btn_Create_Click(object sender, EventArgs e)
    {
        OpenDataBase(true);
        ds = ExecuteCreateDo(this.LB_TabName.SelectedItem.Text);
        if (Common.CheckDataSet(ds))
        {
            if (ds.Tables[0].Rows[0]["zhujian"].ToString() != "√")
            {
                Common.Alert(this.Page, string.Format("表{0}缺少主键,生成失败", this.LB_TabName.SelectedItem.Text));
            }
            else if (string.IsNullOrEmpty(this.Txt_TabName.Text.Trim()))
            {
                Common.Alert(this.Page, "请填入表说明");
            }
            else
            {
                PrimaryKey = ds.Tables[0].Rows[0]["_name"].ToString();
                CreateString(ds);
            }
        }
    }

    /// <summary>
    /// 执行CreateDo存储过程，查询表相关信息
    /// </summary>
    public DataSet ExecuteCreateDo(string TabName)
    {
        OpenDataBase(true);
        DataSet ds1 = new DataSet();
        using (SqlCommand com = new SqlCommand())
        {
            try
            {
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = string.Format(SelectSystemSql, TabName);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                sda.Fill(ds1);
                sda.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
                //Common.Alert(this.Page, "缺少存储过程“usp_CreateDO”");
                // return;
            }
        }
        return ds1;
    }

    public void CreateString(DataSet ds)
    {
        sb = new StringBuilder();
        sb.Append("using System;\n\n");
        sb.Append("namespace ZD_DataModel \n{\n");
        sb.AppendFormat(@"    [ZD_TabName(""{0}"", ""{1}"", ""{2}"", true)]", this.LB_TabName.SelectedItem.Text, this.Txt_TabName.Text, PrimaryKey);
        sb.AppendLine();
        sb.AppendFormat("    public class {0} : ZD_DataModelBase\n", this.LB_TabName.SelectedItem.Text);
        sb.Append("    {\n");
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            sb.AppendLine("        /// <summary>");
            if (!string.IsNullOrEmpty(dr["Remark"].ToString()))
            {
                sb.AppendLine("        /// " + dr["Remark"].ToString() + "");
            }
            else
            {
                sb.AppendLine("        /// 暂无说明");
            }
            sb.AppendLine("        /// </summary>");
            if (dr["_name"].ToString().Equals("StartTime") && GetDataType(dr["type"].ToString()).Equals("DateTime"))
            {
                sb.Append("        public ");
                sb.Append(GetDataType(dr["type"].ToString()) + " ");
                sb.Append(dr[2]);
                sb.Append(" { get { return DateTime.Now; } set { ;} }\n");
            }
            else
            {
                sb.Append("        public ");
                sb.Append(GetDataType(dr["type"].ToString()) + " ");
                sb.Append(dr[2]);
                sb.Append(" { get; set; }\n");
            }
        }
        sb.Append("    }\n");
        sb.Append("}");
        CreateFile();
    }

    public void CreateFile()
    {
        string str = Server.MapPath(string.Format("~/ZD_DataModel/ZD_DataModel/{0}.cs", this.LB_TabName.SelectedItem.Text));
        str = str.Replace("ZD_Web\\", "");
        StreamWriter sw = new StreamWriter(str);
        sw.Write(sb.ToString());
        sw.Dispose();
        sw.Close();
        Common.Alert(this.Page, "生成成功");
    }

    public string GetDataType(string type)
    {
        switch (type)
        {
            case "int":
                return "int?";
            case "tinyint":
                return "int?";
            case "char":
                return "string";
            case "nvarchar":
                return "string";
            case "varchar":
                return "string";
            case "smalldatetime":
                return "DateTime?";
            case "datetime":
                return "DateTime?";
            case "decimal":
                return "decimal?";
            case "float":
                return "float?";
            case "numeric":
                return "decimal?";
            case "ntext":
                return "string";
            case "text":
                return "string";
            case "uniqueidentifier":
                return "Guid?";
            default:
                return "";
        }
    }

    protected void LB_DataBase_SelectedIndexChanged(object sender, EventArgs e)
    {
        OpenDataBase(true);
        if (LB_DataBase.SelectedItem.Text != "")
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.CommandText = string.Format("SELECT name,id FROM [{0}] ..sysobjects  "
            + " WHERE type='U' AND name <> 'dtproperties' "
            + " ORDER BY name ", LB_DataBase.SelectedItem.Text);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                sda.Fill(ds);
                sda.Dispose();
            }
            this.LB_TabName.DataTextField = "name";
            this.LB_TabName.DataValueField = "id";
            this.LB_TabName.DataSource = ds.Tables[0];
            this.LB_TabName.DataBind();
        }
    }

    protected void LB_TabName_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    string SelectSystemSql = @"SELECT 
    (case when a.colorder=1 then d.name else '' end) TableName,
    a.colorder number,
    a.name _name,
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) biaoshi,
    (case when (SELECT count(*) 
                FROM sysobjects 
                WHERE (name in (SELECT name
                                FROM sysindexes
                                WHERE (id = a.id) AND (indid in (SELECT indid
                                                
                 FROM sysindexkeys
                                                                 WHERE (id = a.id) AND (colid in (SELECT colid
                                                                                                  FROM syscolumns
                                                                                                  WHERE (id = a.id) AND (name = a.name)
                                                                                                  )
                                                                                        )
            
                                                    )
                                                        )
                                )
                        ) AND (xtype = 'PK') 
                ) > 0 then '√' else '' end) zhujian,
    b.name type,
    a.length typeLength,
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as lenght,
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as f,
    (case when a.isnullable=1 then '√'else '' end) OkNull,
    isnull(e.text,'') defaultValue,
    isnull(g.[value],
'') AS Remark,
	(select (select [Name] from sysobjects where id=ForeignKeys.rkeyid) as ForigenKeyTable from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as ConstraintView
inner join sysobjects as Object
on ConstraintView.Constraint_Name=Object.[Name]
inner join sysforeignkeys as ForeignKeys
on Object.Id=ForeignKeys.constid
where ConstraintView.column_name=a.[name] and table_name=d.[name]) as ForigenKeyTable

FROM  syscolumns a 
    left join systypes b on a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id  and  d.xtype='U' and d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id  where d.name='{0}'
order by a.id,a.colorder";
}